import sqlite3
import pandas as pd
import redis
import json
from pyecharts import Bar,Pie,Line


if __name__ == '__main__':

    db_name = '../db.sqlite3'

else:

    db_name = 'db.sqlite3'

class douban():
    def __init__(self):
        self.conn = sqlite3.connect(db_name)
        self.r=redis.Redis(host='192.168.142.129', port=6379, password = '1', db = '0')
        # self.df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
    def series_to_bar(self, atrr,v1,name = 'test',):
        bar = Bar(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i),2) for i in v1]
        bar.add(name, atrr, v1, is_label_show=True, is_datazoom_show=True)
        return bar
    def series_to_line(self, atrr, v1, name='test'):
        line = Line(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i), 2) for i in v1]
        print(2222)
        print(atrr)
        print(v1)
        line.add(name, atrr, v1)
        return line
    def series_to_pie(self,atrr,v1,name = 'test'):
        pie=Pie(name)
        atrr = [str(i, encoding='utf-8') for i in atrr]
        v1 = [round(float(i), 2) for i in v1]
        pie.add(name, atrr, v1,is_label_show=False)
        # print(pie)
        return pie
    def make_everv_movie_user_comment_num(self):
        attr = self.r.hkeys('make_everv_movie_user_comment_num')
        v1 = self.r.hvals('make_everv_movie_user_comment_num')
        if not attr:
            print(1)
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=df.groupby(['Movie_Name_CN']).Username.count().to_dict()
            self.r.hmset('make_everv_movie_user_comment_num', info)
            attr = self.r.hkeys('make_everv_movie_user_comment_num')
            v1 = self.r.hvals('make_everv_movie_user_comment_num')
            print(v1)
        print(v1)
        a=self.series_to_bar(attr,v1,name='每部电影评论用户')
        return a
    def make_every_movie_like_num(self):
        attr = self.r.hkeys('make_every_movie_like_num')
        v1 = self.r.hvals('make_every_movie_like_num')
        if not attr:
            print(1)
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=df.groupby(['Movie_Name_CN']).Like.sum().to_dict()
            self.r.hmset('make_every_movie_like_num', info)
            attr = self.r.hkeys('make_every_movie_like_num')
            v1 = self.r.hvals('make_every_movie_like_num')
        a = self.series_to_bar(attr, v1, name='每部电影点赞数量')
        return a
    def get_like_most_user(self):
        most=self.r.get('get_like_most_user')
        if not most:
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=df.sort_values('Like',ascending=False).Username[0]
            self.r.set('get_like_most_user',1)
        print(most)
    def make_every_movie_star_bar(self):
        attr = self.r.hkeys('make_every_movie_star_bar')
        v1 = self.r.hvals('make_every_movie_star_bar')
        if not attr:
            print(1)
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=df.groupby(['Movie_Name_CN']).Star.mean().to_dict()
            self.r.hmset('make_every_movie_star_bar', info)
            attr = self.r.hkeys('make_every_movie_star_bar')
            v1 = self.r.hvals('make_every_movie_star_bar')
            # print(info)
        # print(attr)
        # print(v1)
        a = self.series_to_bar(attr, v1, name='每部电影平均分数')
        return a
    def make_real_every_movie_star_bar(self):
        attr = self.r.hkeys('make_real_every_movie_star_bar')
        v1 = self.r.hvals('make_real_every_movie_star_bar')
        if not attr:
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info = df[df.Like > 100].groupby(['Movie_Name_CN']).Star.mean().to_dict()
            self.r.hmset('make_real_every_movie_star_bar', info)
            attr = self.r.hkeys('make_real_every_movie_star_bar')
            v1 = self.r.hvals('make_real_every_movie_star_bar')
        a = self.series_to_bar(attr, v1, name='每部电影真实平均分数')
        return a
    def make_most_20_user_comment_bar(self):
        attr = self.r.hkeys('make_most_20_user_comment')
        v1 = self.r.hvals('make_most_20_user_comment')
        if not attr:
            df = pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info = df.groupby('Username').count().ID.sort_values(ascending=False).head(20).to_dict()
            # print(info)
            self.r.hmset('make_most_20_user_comment', info)
            attr = self.r.hkeys('make_most_20_user_comment')
            v1 = self.r.hvals('make_most_20_user_comment')
            print('success')
        a = self.series_to_bar(attr, v1, name='发布评论最多的前20个用户及对应的发布量')
        return a
    def get_every_movie_comment_like_5(self):
        if not self.r.exists('get_every_movie_comment_like_5'):
            print(1)
            info=pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            movie = info.Movie_Name_CN.value_counts().index
            for i in movie:
                a = pd.read_sql('select * from DmSc_csv where Movie_Name_CN = "{}"'.format(i), self.conn)
                dic=a.set_index('Username').sort_values('Like',ascending=False).Comment.head(5).to_dict()
                dic=json.dumps(dic)
                self.r.hset('get_every_movie_comment_like_5',i,dic)
                print(dic)
        return self.r.hgetall('get_every_movie_comment_like_5')


    def get_every_star_lower_2_and_like_3(self):
        if not self.r.exists('get_every_star_lower_2_and_like_3'):
            print(1)
            info=pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            movie = info.Movie_Name_CN.value_counts().index
            for i in movie:
                a = pd.read_sql('select * from DmSc_csv where Movie_Name_CN = "{}"'.format(i), self.conn)
                dic=a[a.Star <= 2].set_index('Username').sort_values('Like',axis=0,ascending=False).head(3)['Comment'].to_dict()
                dic=json.dumps(dic)
                self.r.hset('get_every_star_lower_2_and_like_3',i,dic)
                print(dic)
        return self.r.hgetall('get_every_star_lower_2_and_like_3')
    def make_line_fuchou_every_day_comment_num(self):
        if not self.r.exists('make_line_fuchou_every_day_comment_num'):
            info=pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=info[info.Movie_Name_CN == '复仇者联盟2'].groupby('Date').count()['Like'].to_dict()
            self.r.hmset('make_line_fuchou_every_day_comment_num',info)
            print(info)
        attr = self.r.hkeys('make_line_fuchou_every_day_comment_num')
        v1 = self.r.hvals('make_line_fuchou_every_day_comment_num')
        print(attr)
        print(v1)
        a=self.series_to_line(attr,v1,name='复仇者联盟2电影的每天评论的数量')
        print(a)
        return a
    def get_dark_man(self):
        if not self.r.exists('get_dark_man'):
            info=pd.read_sql('SELECT * FROM DmSC_csv', self.conn)
            info=info[info.Star == 1].groupby('Username').count().sort_values('ID',ascending=False).index[0]
            self.r.set('get_dark_man',info)
            print(info)
        return self.r.get('get_dark_man')
do_obj=douban()
# x.get_every_star_lower_2_and_like_3()
# x.make_line_fuchou_every_day_comment_num()
# print(x.get_dark_man())
a=do_obj.get_every_movie_comment_like_5()
print(a)
# print(a)
b={}

for i,k in a.items():
    key=i.decode()
    vv=str(json.loads(k)).replace('{','').replace('}','')
    vvv=[key]+vv.split(',')
    print(type(vv))
    b[key]=vvv
print(b)

    # b[key]=vvv
# print(type(b))

# print(ddd)
# print(b)
# print(b)
# cc=[]
# for i,k in b.items():
#     print(i)
#     print(cc.append([i]+k))
# print(cc)
# for i in cc:
#     print(i)
# for i,k in b.items():
#     print(i)
#     print(type(i))
#     print(list(k.values()))
#     print()
#     print(type(k))